/*
 * # 应用公共代码:
 *  Authority - 组织与用户
 * 
 * # 描述:
 *  为应用提供组织、工作岗位、用户的管理功能，以及针对组织、部门、岗位和用户的权限分派和验证功能，同时提供用户登录、注销、导航等基础功能。
 * 
 * Copyright (c), 上海沃恩信息科技有限公司.
 * All rights reserved.
 * Licensed under BSD (https://www.pittypat.work/bsd.html).
 */

use "pittypat_app";
go

/*
 * # 公共函数:
 *  uuid_to_b64 - 将 uniqueidentifier 转换为对 URI 友好的 base64 字符串 char(22)
 * 
 * # 描述:
 *  将 uniqueidentifier 转换为对 URI 友好的 base64 字符串 char(22)。
 *  大多数情况下，客户端使用 base64 char(22)，如果要向客户端返回字符串格式的 uniqueidentifier，应该使用该函数对 uniqueidentifier 进行转换。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."uuid_to_b64"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."uuid_to_b64"(@id uniqueidentifier) returns char(22) begin return null; end');
go

alter function "waln_authority"."uuid_to_b64"(@id uniqueidentifier)
returns char(22)
begin
	if @id is not null
	begin
		declare @bin varbinary(16) = convert(varbinary(16), replace(convert(varchar(36), @id), '-', ''), 2);
		return replace(replace(substring(cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(24)'), 1, 22), '/', '_'), '+', '-');
	end

	return null;
end
go

/*
 * # 公共函数:
 *  b64_to_uuid - 将对 URI 友好的 base64 char(22) uuid 转换为 uniqueidentifier
 * 
 * # 描述:
 *  将对 URI 友好的 base64 char(22) uuid 转换为 binary(16)。
 *  如果从客户端传来的值是 base64 char(22)，则应该使用该函数将字符串转换为 uniqueidentifier。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."b64_to_uuid"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."b64_to_uuid"(@id char(22)) returns uniqueidentifier begin return null; end');
go

alter function "waln_authority"."b64_to_uuid"(@id char(22))
returns uniqueidentifier
begin
	if @id is not null
	begin
		declare @b64 varchar(24) = replace(replace(@id, '-', '+'), '_', '/') + '==';
		declare @s varchar(36) = convert(varchar(36), cast(N'' as xml).value('xs:base64Binary(sql:variable("@b64"))', 'varbinary(16)'), 2);
		return cast(substring(@s, 1, 8) + '-' + substring(@s, 9, 4) + '-' + substring(@s, 13, 4) + '-' + substring(@s, 17, 4) + '-' + substring(@s, 21, 12) as uniqueidentifier);
	end

	return null;
end
go

/*
 * # 公共函数:
 *  like_escape - 转义用于 LIKE 运算符的字符串
 * 
 * # 描述:
 *  转义用于 LIKE 运算符的字符串：
 *  如果字符串是 null 或空字符串，则返回 null，
 *  否则分别使用 '[_]'、'[[]' 和 '[%]' 替换字符串中的 '_'、'[' 和 '%'，并在字符串前面和后面分别添加 '%'。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."like_escape"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."like_escape"() returns nvarchar(max) begin return null; end');
go

alter function "waln_authority"."like_escape"(@s nvarchar(max))
returns nvarchar(max)
begin
	if @s is null or @s = N''
		return null;
	return N'%' + replace(replace(replace(@s, N'"', N'"""'), N'_', N'"_"'), N'%', N'"%"') + N'%';
end
go

/*
 * # 公共存储过程:
 *  reg_app - 注册安装一个新的 App
 * 
 * # 描述:
 *  注册安装一个新的 App。如果参数无效或者相同应用已经存在，则产生异常错误。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."reg_app"') and "type" in (N'P', N'PC'))
	exec('create procedure "waln_authority"."reg_app" as begin set nocount on; end');
go

alter procedure "waln_authority"."reg_app"
	@id uniqueidentifier, -- Id
	@developerCode varchar(30), -- 开发者代码
	@developerName nvarchar(50) /* 开发者名称 */, 
	@identifier varchar(30), -- 标识符
	@name nvarchar(50), -- 名称
	@version varchar(24) /* 版本号 */, 
	@moduleCount smallint /* 模块数目 */, 
	@description nvarchar(2000), -- 描述
	@position int /* 位置 */
as
begin
	set nocount on;

	if  @id is null or @developerCode is null or @developerCode = '' or
		@developerName is null or @developerName = '' or 
        @identifier is null or @identifier = '' or 
        @name is null or @name = '' or 
        @version is null or @version = '' or
        @moduleCount is null or @moduleCount < 0
	begin
        raiserror(N'没有提供为注册应用有效的参数，Id、开发者代码、开发者名称、标识符、名称、版本号都不能为 NULL 或空字符串，模块数目不能为 NULL 或小于 0。', 10, 1);
        return;
	end

	if exists (select * from "waln_authority"."apps" where "Id" = @id)
	begin
		-- 检查相同开发者代码和标识符的应用是否已经被注册
		if exists (select * from "waln_authority"."apps" where "DeveloperCode" = @developerCode and "Identifier" = @identifier and "Id" <> @id)
		begin
			raiserror(N'同一开发者代码下的相同标识符的应用已经被注册。', 10, 1);
			return;
		end
		
        -- 检查相同位置是否被占用，如果被占用，则保持当前位置不变
        if @position is null or @position < 0 or exists (select "Id" from "waln_authority"."apps" where "Position" = @position)
		begin
			set @position = null;
        end

		update 
			"waln_authority"."apps"
		set
			"DeveloperCode" = @developerCode,
			"DeveloperName" = @developerName,
			"Identifier" = @identifier,
			"Name" = @name,
			"Version" = @version,
			"ModuleCount" = @moduleCount,
			"Description" = case when @description = '' then null else @description end,
			"Position" = isnull(@position, "Position"),
			"InstallTime" = sysdatetime()
		where
			"Id" = @id;
	end
	else
	begin
		-- 检查相同开发者代码和标识符的应用是否已经被注册
		if exists (select * from "waln_authority"."apps" where "DeveloperCode" = @developerCode and "Identifier" = @identifier)
		begin
			raiserror(N'同一开发者代码下的相同标识符的应用已经被注册。', 10, 1);
			return;
		end
		
		-- 如果没有提供位置，或者位置已经被占用，则默认使用当前最大位置值+1
		if @position is null or @position < 0 or exists (select "Id" from "waln_authority"."apps" where "Position" = @position)
		begin
			select @position = max("Position") from "waln_authority"."apps";
			set @position = isnull(@position, 0) + 1;
		end

		insert into "waln_authority"."apps" 
		(
			"Id", -- Id 
			"DeveloperCode", -- 开发者代码 
			"DeveloperName", -- 开发者名称 
			"Identifier", -- App 标识符 
			"Name", -- App 名称 
			"Version", -- App 版本 
			"ModuleCount", -- 模块数目 
			"Description",  -- 描述 
			"Position", -- 位置 
			"InstallTime" -- 安装时间 
		)
		values
		(
			@id, -- Id 
			@developerCode, -- 开发者代码 
			@developerName, -- 开发者名称 
			@identifier, -- App 标识符 
			@name, -- App 名称 
			@version, -- App 版本 
			@moduleCount, -- 模块数目 
			case when @description = '' then null else @description end,  -- 描述 
			@position, -- 位置 
			sysdatetime() -- 安装时间 
		);
	end
end
go

/*
 * # 公共存储过程:
 *  reg_module - 注册安装一个新的模块
 * 
 * # 描述:
 *  注册安装一个新的模块。如果参数无效或者相同模块已经存在，则产生异常错误。
 */
 
if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."reg_module"') and "type" in (N'P', N'PC'))
	exec('create procedure "waln_authority"."reg_module" as begin set nocount on; end');
go

alter procedure "waln_authority"."reg_module"
	@id uniqueidentifier, -- Id
	@appId uniqueidentifier /* App Id */, 
	@position smallint, -- 位置
	@identifier varchar(30), -- 标识符
	@name nvarchar(50), -- 名称
	@presentation tinyint /* 呈现方式：0-内部，1-UI，2-服务 */, 
	@allowAnonymous bit /* 是否允许匿名用户访问 */, 
	@description nvarchar(2000) /* 描述 */
as
begin
	set nocount on;

	if  @id is null or @appId is null or
		@position is null or @position < 0 or 
        @identifier is null or @identifier = '' or 
        @name is null or @name = '' or 
        @presentation is null or @presentation not in (0, 1, 2) or
        @allowAnonymous is null
	begin
        raiserror(N'没有提供为注册模块有效的参数，Id、App Id、标识符、名称都不能为 NULL 或空字符串，位置不能为 NULL 或小于 0，呈现方式必须为 0、1、2 之一。', 10, 1);
        return;
	end

	-- 检查 App 是否已经注册
    if not exists (select * from "waln_authority"."apps" where "Id" = @appId)
	begin
        raiserror(N'没有找到模块隶属的应用。', 10, 1);
        return;
    end
	
    if exists (select * from "waln_authority"."modules" where "Id" = @id)
	begin
		-- 检查相同标识符的模块是否已经存在
		if exists (select * from "waln_authority"."modules" where "AppId" = @appId and "Identifier" = @identifier and "Id" <> @id)
		begin
			raiserror(N'目标应用中已经包含相同标识符的模块。', 10, 1);
			return;
		end

		update
			"waln_authority"."modules" 
		set
			"Identifier" = @identifier,
			"Name" = @name,
			"Presentation" = @presentation,
			"AllowAnonymous" = @allowAnonymous,
			"Description" = case when @description = '' then null else @description end,
			"Position" = @position
		where
			"Id" = @id;
	end
	else
	begin
		-- 检查相同标识符的模块是否已经存在
		if exists (select * from "waln_authority"."modules" where "AppId" = @appId and "Identifier" = @identifier)
		begin
			raiserror(N'目标应用中已经包含相同标识符的模块。', 10, 1);
			return;
		end

		insert into "waln_authority"."modules" 
		(
			"Id", -- Id 
			"AppId", -- 隶属 App 的 Id 
			"Position", -- 位置 
			"Identifier", -- 标识符 
			"Name", -- 模块名称 
			"Presentation", -- 呈现方式 
			"AllowAnonymous", -- 允许匿名访问 
			"Description"  -- 描述 
		)
		values
		(
			@id, -- Id 
			@appId, -- 隶属 App 的 Id 
			@position, -- 位置 
			@identifier, -- 标识符 
			@name, -- 模块名称 
			@presentation, -- 呈现方式：0-不公开，1-UI，2-服务 
			@allowAnonymous, -- 允许匿名访问 
			case when @description = '' then null else @description end  -- 描述 
		);
	end
end
go

/*
 * # 公共函数:
 *  bin_xor - binary 异或
 * 
 * # 描述:
 *  计算两个 binary 值的异或。
 */
 
if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."bin_xor"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."bin_xor"() returns bigint begin return 0; end');
go

alter function "waln_authority"."bin_xor"(@x varbinary(64), @y varbinary(64), @len int)
returns varbinary(64)
begin
	declare @res varbinary(64);
	declare @i int = 1;

	while @i <= @len
	begin
		if @i = 1
			set @res = cast((cast(substring(@x, @i, 4) as int) ^ cast(substring(@y, @i, 4) as int)) as binary(4));
		else
			set @res = @res + cast((cast(substring(@x, @i, 4) as int) ^ cast(substring(@y, @i, 4) as int)) as binary(4));
		set @i = @i + 4;
	end

	return @res;
end
go

/*
 * # 公共函数:
 *  encrypt_password - 加密密码
 * 
 * # 描述:
 *  将明文密码加密为密文。此加密过程是不可逆的，加密后的密文只能用于验证，而不能解密。
 */
 
if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."encrypt_password"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."encrypt_password"() returns bigint begin return 0; end');
go

if object_id(N'"waln_authority"."_v_random_bytes"', N'V') is null
	exec('create view "waln_authority"."_v_random_bytes" as select random_bytes = crypt_gen_random(16)');
go

alter function "waln_authority"."encrypt_password"(@pswd varchar(30))
returns binary(36)
begin
	-- 密码明文在右边补充 '0' 之后的值，作为 hmac 的 key
	declare @skey binary(64);
    -- skey 与 0x36...36 的异或
	declare @ipad binary(64);
    -- skey 与 0x5c...5c 的异或
    declare @opad binary(64);
    
    -- 随机的二进制 salt
	declare @salt varbinary(20);
    -- 计算得到的 hash 代码
    declare @hash binary(20);
    -- 中间计算的 hash 代码
    declare @tmp binary(20);
    -- 最终的密文
    declare @cipher binary(36);
    -- 循环计数
    declare @i int = 0;
    
    -- 密码明文在右边补充 '0' 之后的值，作为 hmac 的 key
    set @skey = convert(binary(128), @pswd);

    -- skey 与 0x36...36 的异或
	set @ipad = "waln_authority"."bin_xor"(@skey, 0x36363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636, 64);

	-- skey 与 0x5c...5c 的异或
	set @opad = "waln_authority"."bin_xor"(@skey, 0x5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c, 64);
    
    -- 创建 16 个字节的随机 salt
    select @salt = random_bytes from "waln_authority"."_v_random_bytes";
    -- 将密码作为 key，计算 salt 的 hash
    set @hash = hashbytes('SHA1', @opad + hashbytes('SHA1', @ipad + cast(@salt as binary(20))));
    -- 将密码作为 key，计算 _hash 的 hash，计算 4096 次
    while @i < 4096
	begin
		set @tmp = hashbytes('SHA1', @opad + hashbytes('SHA1', @ipad + @hash));
        -- 计算与上次 hash 的异或
        set @hash = "waln_authority"."bin_xor"(@tmp, @hash, 20);
		set @i = @i + 1;
    end

    -- 将 salt 与最后的 hash 拼接在一起作为密文返回
    set @cipher = @salt + @hash;

    return @cipher;
end
go

/*
 * # 公共函数:
 *  check_password - 验证密码
 * 
 * # 描述:
 *  使用指定的密文验证一个明文密码是否有效，密码区分大小写。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."check_password"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."check_password"() returns bigint begin return 0; end');
go

alter function "waln_authority"."check_password"(@pswd varchar(30), @cipher binary(36))
returns bit
begin
	-- 密码明文在右边补充 '0' 之后的值，作为 hmac 的 key
	declare @skey binary(64);
    -- skey 与 0x36...36 的异或
	declare @ipad binary(64);
    -- skey 与 0x5c...5c 的异或
    declare @opad binary(64);
    
    -- 随机的二进制 salt
	declare @salt varbinary(20);
    -- 计算得到的 hash 代码
    declare @hash binary(20);
    -- 中间计算的 hash 代码
    declare @tmp binary(20);
    -- 循环计数
    declare @i int = 0;
    
    -- 密码明文在右边补充 '0' 之后的值，作为 hmac 的 key
    set @skey = convert(binary(128), @pswd);

    -- skey 与 0x36...36 的异或
	set @ipad = "waln_authority"."bin_xor"(@skey, 0x36363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636, 64);

	-- skey 与 0x5c...5c 的异或
	set @opad = "waln_authority"."bin_xor"(@skey, 0x5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c5c, 64);
    
    -- 创建 16 个字节的随机 salt
    set @salt = substring(@cipher, 1, 16);
    -- 将密码作为 key，计算 salt 的 hash
    set @hash = hashbytes('SHA1', @opad + hashbytes('SHA1', @ipad + cast(@salt as binary(20))));
    -- 将密码作为 key，计算 _hash 的 hash，计算 4096 次
    while @i < 4096
	begin
		set @tmp = hashbytes('SHA1', @opad + hashbytes('SHA1', @ipad + @hash));
        -- 计算与上次 hash 的异或
        set @hash = "waln_authority"."bin_xor"(@tmp, @hash, 20);
		set @i = @i + 1;
    end

    return case when substring(@cipher, 17, 20) = @hash then 1 else 0 end;
end
go

/*
 * # 公共函数:
 *  is_identifier - 检查字符串是否是有效的标识符
 * 
 * # 描述:
 *  检查字符串是否是有效的标识符，有效的标识符满足以下条件：
 *  1、长度不能超过最大长度的限制，最大不能超过 63。
 *  2、第一个字符必须是字母。
 *  3、其他字符可以是数字、字母或下划线。
 */
 
if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."is_identifier"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."is_identifier"() returns bigint begin return 0; end');
go

alter function "waln_authority"."is_identifier"(@s varchar(63), @m int)
returns bit
begin
	declare @c int;
	declare @p int;

    if @s is null or @s = '' or len(@s) > @m
		return 0;

	-- 首字符必须是字母
	set @c = ascii(@s);
	if not ((@c >= 65 and @c <= 90) or (@c >= 97 and @c <= 122))
		return 0;

	-- 其他字符必须是字母、数字或下划线
	set @p = 2;
	while @p <= len(@s)
	begin
		set @c = ascii(substring(@s, @p, 1));
		if not ((@c = 95) or (@c >= 48 and @c <= 57) or (@c >= 65 and @c <= 90) or (@c >= 97 and @c <= 122))
			return 0;
		set @p = @p + 1;
	end

    return 1;
end
go

/*
 * # 公共函数:
 *  is_mobile - 检查指定的字符串是否是有效的手机号码
 * 
 * # 描述:
 *  检查指定的字符串是否是有效的手机号码。
 */
 
if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."is_mobile"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."is_mobile"() returns bigint begin return 0; end');
go

alter function "waln_authority"."is_mobile"(@s varchar(20))
returns bit
begin
	declare @c int;
	declare @p int;

    if @s is null or len(@s) <> 11
		return 0;

	-- 首字符必须为 1
	set @c = ascii(@s);
	if @c <> 49
		return 0;

	-- 第二个字符必须为 3,4,5,7,8
	set @c = ascii(substring(@s, 2, 1));
	if @c not in (51, 52, 53, 55, 56)
		return 0;

	-- 其他字符必须是数字
	set @p = 3;
	while @p <= len(@s)
	begin
		set @c = ascii(substring(@s, @p, 1));
		if not (@c >= 48 and @c <= 57)
			return 0;
		set @p = @p + 1;
	end

	return 1;
end
go

/*
 * # 公共函数:
 *  is_email - 检查指定的字符串是否是有效的电子邮件地址
 * 
 * # 描述:
 *  检查指定的字符串是否是有效的电子邮件地址。
 */
 
if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."is_email"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_authority"."is_email"() returns bigint begin return 0; end');
go

alter function "waln_authority"."is_email"(@s varchar(50))
returns bit
begin
    if @s is null or @s = ''
		return 0;
    return case when @s like '%_@__%.__%' then 1 else 0 end;
end
go

/*
 * # 公共函数:
 *  _delete_dept - 递归删除一个部门和所有下级部门、岗位等
 * 
 * # 描述:
 *  递归删除一个部门和所有下级部门、岗位等。
 */
 
if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_authority"."_delete_dept"') and "type" in (N'P', N'PC'))
	exec('create procedure "waln_authority"."_delete_dept" as begin set nocount on; end');
go

alter procedure "waln_authority"."_delete_dept"
    @id uniqueidentifier, -- Id
	@level tinyint -- 层级
as
begin
	-- 不自动向终端返回受影响行数的计数
	set nocount on;

	-- 当还没有达到最大层次时，可能存在子级，此时需要删除子级部门
	if @level < 7 
	begin
		-- 子级部门的 Id
		declare @childId uniqueidentifier;
		-- 子级部门的层级
		declare @childLevel tinyint;
		-- 游标需要声明为 local，因为 cur 游标将会递归执行
		declare cur cursor local for select "Id", "Level" from "waln_authority"."depts" where "ParentId" = @id;
    
		-- 打开游标
		open cur;

		fetch next from cur into @childId, @childLevel;
		while @@FETCH_STATUS = 0
		begin
			-- 递归删除下级部门
			exec "waln_authority"."_delete_dept" @childId, @childLevel;
			-- 下一个部门
			fetch next from cur into @childId, @childLevel;
		end

		-- 关闭游标
		close cur;
		-- 释放游标资源
		deallocate cur;
	end;
    
    -- 删除与部门相关的权限
	delete from "waln_authority"."permissions" where "RoleType" = 2 and "RoleId" = @id;
    
    -- 删除与部门中岗位相关的权限
	delete p from "waln_authority"."permissions" as p 
	join "waln_authority"."posts" as d on d."Id" = p."RoleId" and p."RoleType" = 3 
	where d."DeptId" = @id;
    
    -- 删除部门中的岗位
    delete from "waln_authority"."posts" where "DeptId" = @id;
	
	-- 删除与部门或部门中岗位相关的用户
	delete from "waln_authority"."userposts" where "DeptId" = @id;
    
    -- 删除部门本身
    delete from "waln_authority"."depts" where "Id" = @id;
end
go
